﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.EntityClient;
using CongregacaoOs.Data;

namespace CongregacaoOs.Business
{
    [System.ComponentModel.DataObject]
    public class ccOsBus : IDisposable
    {

        public void Dispose() { }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        public int OsSaveOrUpdate(ccOs os)
        {
            using (EntityConnection conn = new EntityConnection("name = CcbOrdemServico"))
            {
                try
                {
                    using (EntityCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "CcbOrdemServico.ccOsSave";
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        EntityParameter param;
                        /*Dados Gerais da OS*/
                        param = cmd.Parameters.Add("IDOS", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idOs != 0 && os.idOs != null)
                            param.Value = os.idOs;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("TITULO", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.titulo != null && os.titulo != "")
                            param.Value = os.titulo;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("TEXTO", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.texto != null && os.texto != "")
                            param.Value = os.texto;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("DATACRIACAO", DbType.DateTime);
                        param.Direction = ParameterDirection.Input;
                        if (os.dataCriacao != null && os.dataCriacao != DateTime.MinValue)
                            param.Value = os.dataCriacao;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("DATAPRAZO", DbType.DateTime);
                        param.Direction = ParameterDirection.Input;
                        if (os.dataPrazo != null && os.dataPrazo != DateTime.MinValue)
                            param.Value = os.dataPrazo;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDTIPOOS", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idTipoOs != 0 && os.idTipoOs != null)
                            param.Value = os.idTipoOs;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("CODIGO", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.codigo != null && os.codigo != "")
                            param.Value = os.codigo;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("STATUS", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.status != null && os.status != "")
                            param.Value = os.status;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("EnviarEmailSetor", DbType.Boolean);
                        param.Direction = ParameterDirection.Input;
                        if (os.EnviarEmailSetor != null)
                            param.Value = os.EnviarEmailSetor;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("ANEXO", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.anexo != null && os.anexo != "")
                            param.Value = os.anexo;
                        else
                            param.Value = DBNull.Value;

                        /*Dados do Usuário que Abriu */
                        param = cmd.Parameters.Add("IDSERVOQABRIU", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idServoQAbriu != 0 && os.idServoQAbriu != 0)
                            param.Value = os.idServoQAbriu;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("idRegionalIn", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idRegionalIn != 0 && os.idRegionalIn != 0)
                            param.Value = os.idRegionalIn;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("idDistritalIn", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idDistritalIn != 0 && os.idDistritalIn != null)
                            param.Value = os.idDistritalIn;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("idAdmIn", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idAdmIn != 0 && os.idAdmIn != null)
                            param.Value = os.idAdmIn;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDCIDADEIN", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idCidadeIn != 0 && os.idCidadeIn != null)
                            param.Value = os.idCidadeIn;
                        else
                            param.Value = DBNull.Value;

                        /*Dados da Cidade Responsável*/
                        param = cmd.Parameters.Add("idRegionalOut", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idRegionalOut != 0 && os.idRegionalOut != null)
                            param.Value = os.idRegionalOut;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("idDistritalOut", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idDistritalOut != 0 && os.idDistritalOut != null)
                            param.Value = os.idDistritalOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("idAdmOut", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idAdmOut != 0 && os.idAdmOut != null)
                            param.Value = os.idAdmOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDCIDADEOUT", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idCidadeOut != 0 && os.idCidadeOut != null)
                            param.Value = os.idCidadeOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDSETOROUT", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idSetorOut != 0 && os.idSetorOut != null)
                            param.Value = os.idSetorOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("idServoQExecutara", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idServoQExecutara != 0 && os.idServoQExecutara != null)
                            param.Value = os.idServoQExecutara;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("idOsOutPut", DbType.Int32);
                        param.Direction = ParameterDirection.Output;

                        using (conn) 
                        {
                            if(conn.State == ConnectionState.Closed) conn.Open();

                            cmd.ExecuteNonQuery();
                            int aRetorn = 0;

                            if (cmd.Parameters["idOsOutPut"].Value != DBNull.Value)
                            aRetorn = cmd.Parameters["idOsOutPut"].Value != null ? Convert.ToInt32(cmd.Parameters["idOsOutPut"].Value) : 0; 
                            
                                   return aRetorn;
                        }
                    }
                }
                catch (Exception e)
                {
                    return 0;
                    throw new Exception("Ocorreu um erro:" + e.Message);
                    
                }

            }
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public List<ccOsFull1_Result3> OsSelectFull(ccOs os, DateTime aDataFiltro)
        {
            using (EntityConnection conn = new EntityConnection("name = CcbOrdemServico"))
            {
                try
                {
                    using (EntityCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "CcbOrdemServico.ccOsFull1";
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;

                        EntityParameter param;

                        param = cmd.Parameters.Add("IDOS", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idOs != 0)
                            param.Value = os.idOs;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("CODIGO", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.codigo != "" && os.codigo != null)
                            param.Value = os.codigo;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("DATACRIACAO", DbType.DateTime);
                        param.Direction = ParameterDirection.Input;
                        if (os.dataCriacao != DateTime.MinValue && os.dataCriacao != null)
                            param.Value = os.dataCriacao;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("DATAFILTRO", DbType.DateTime);
                        param.Direction = ParameterDirection.Input;
                        if (aDataFiltro != DateTime.MinValue && aDataFiltro != null)
                            param.Value = aDataFiltro;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("IDTIPOOS", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idTipoOs != 0 && os.idTipoOs != null)
                            param.Value = os.idTipoOs;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("IDCIDADEOUT", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idCidadeOut != 0 && os.idCidadeOut != null)
                            param.Value = os.idCidadeOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDCIDADEIN", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idCidadeIn != 0 && os.idCidadeIn != null)
                            param.Value = os.idCidadeIn;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDSETOR", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idSetorOut != 0 && os.idSetorOut != null)
                            param.Value = os.idSetorOut;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("STATUS", DbType.String);
                        param.Direction = ParameterDirection.Input;
                        if (os.status != null && os.status != "")
                            param.Value = os.status;
                        else
                            param.Value = DBNull.Value;


                        /******* Dados de Distrital e Regional  *******/
                        param = cmd.Parameters.Add("IDDISTRITALIN", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idDistritalIn != 0 && os.idDistritalIn != null)
                            param.Value = os.idDistritalIn;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDDISTRITALOUT", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idDistritalOut != 0 && os.idDistritalOut != null)
                            param.Value = os.idDistritalOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDREGIONALIN", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idRegionalIn != 0 && os.idRegionalIn != null)
                            param.Value = os.idRegionalIn;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDREGIONALOUT", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idRegionalOut != 0 && os.idRegionalOut != null)
                            param.Value = os.idRegionalOut;
                        else
                            param.Value = DBNull.Value;

                        param = cmd.Parameters.Add("IDSERVOQEXECUTARA", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idServoQExecutara != null && os.idServoQExecutara != 0)
                            param.Value = os.idServoQExecutara;
                        else
                            param.Value = DBNull.Value;


                        param = cmd.Parameters.Add("IDADMOUT", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idAdmOut != null && os.idAdmOut != 0)
                            param.Value = os.idAdmOut;
                        else
                            param.Value = DBNull.Value;

                        /*Este parâmetro sera enviado a base de dados se e somente um usuário 
                         do tipo 'DISTRITAL' estiver logado*/
                        param = cmd.Parameters.Add("IDSERVOQABRIU", DbType.Int32);
                        param.Direction = ParameterDirection.Input;
                        if (os.idServoQAbriu != null && os.idServoQAbriu != 0)
                            param.Value = os.idServoQAbriu;
                        else
                            param.Value = DBNull.Value;

                        using (conn)
                        {
                            if (conn.State == ConnectionState.Closed) conn.Open();

                            using (EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                            {

                                List<ccOsFull1_Result3> _result = new List<ccOsFull1_Result3>();
                                while (reader.Read())
                                {
                                    ccOsFull1_Result3 _os = new ccOsFull1_Result3();
                                    /*************************************** Dados Gerais da O.S ****************************************/
                                    _os.idOs = reader.IsDBNull(reader.GetOrdinal("idOs")) == false ? reader.GetInt32(reader.GetOrdinal("idOs")) : 0;
                                    _os.codigo = reader.IsDBNull(reader.GetOrdinal("codigo")) == false ? reader.GetString(reader.GetOrdinal("codigo")) : "";
                                    if (reader.IsDBNull(reader.GetOrdinal("dataCriacao")) == false) _os.dataCriacao = reader.GetDateTime(reader.GetOrdinal("dataCriacao"));
                                    _os.titulo = reader.IsDBNull(reader.GetOrdinal("titulo")) == false ? reader.GetString(reader.GetOrdinal("titulo")) : "";
                                    _os.texto = reader.IsDBNull(reader.GetOrdinal("texto")) == false ? reader.GetString(reader.GetOrdinal("texto")) : "";
                                    _os.status = reader.IsDBNull(reader.GetOrdinal("status")) == false ? reader.GetString(reader.GetOrdinal("status")) : "";
                                    if (reader.IsDBNull(reader.GetOrdinal("dataPrazo")) == false) _os.dataPrazo = reader.GetDateTime(reader.GetOrdinal("dataPrazo"));
                                    _os.EnviarEmailSetor = reader.IsDBNull(reader.GetOrdinal("EnviarEmailSetor")) == false ? reader.GetBoolean(reader.GetOrdinal("EnviarEmailSetor")) : false;
                                    _os.anexo = reader.IsDBNull(reader.GetOrdinal("anexo")) == false ? reader.GetString(reader.GetOrdinal("anexo")) : "";
                                    _os.TipoOs = reader.IsDBNull(reader.GetOrdinal("TipoOs")) == false ? reader.GetString(reader.GetOrdinal("TipoOs")) : "";


                                    /*************************************** Dados de Quem Gerou a O.S ****************************************/
                                    _os.UsuarioIn = reader.IsDBNull(reader.GetOrdinal("UsuarioIn")) == false ? reader.GetString(reader.GetOrdinal("UsuarioIn")) : "";
                                    _os.IdUsuarioIn = reader.IsDBNull(reader.GetOrdinal("IdUsuarioIn")) == false ? reader.GetInt32(reader.GetOrdinal("IdUsuarioIn")) : 0;
                                    _os.RegionalIn = reader.IsDBNull(reader.GetOrdinal("RegionalIn")) == false ? reader.GetString(reader.GetOrdinal("RegionalIn")) : "";
                                    _os.DistritalIn = reader.IsDBNull(reader.GetOrdinal("DistritalIn")) == false ? reader.GetString(reader.GetOrdinal("DistritalIn")) : "";
                                    _os.AdmIn = reader.IsDBNull(reader.GetOrdinal("AdmIn")) == false ? reader.GetString(reader.GetOrdinal("AdmIn")) : "";
                                    _os.CidadeIn = reader.IsDBNull(reader.GetOrdinal("CidadeIn")) == false ? reader.GetString(reader.GetOrdinal("CidadeIn")) : "";


                                    /*************************************** Dados de Quem Atender a O.s ****************************************/
                                    _os.RegionalOut = reader.IsDBNull(reader.GetOrdinal("RegionalOut")) == false ? reader.GetString(reader.GetOrdinal("RegionalOut")) : "";
                                    _os.DistritalOut = reader.IsDBNull(reader.GetOrdinal("DistritalOut")) == false ? reader.GetString(reader.GetOrdinal("DistritalOut")) : "";
                                    _os.AdmOut = reader.IsDBNull(reader.GetOrdinal("AdmOut")) == false ? reader.GetString(reader.GetOrdinal("AdmOut")) : "";
                                    _os.CidadeOut = reader.IsDBNull(reader.GetOrdinal("CidadeOut")) == false ? reader.GetString(reader.GetOrdinal("CidadeOut")) : "";
                                    _os.SetorOut = reader.IsDBNull(reader.GetOrdinal("SetorOut")) == false ? reader.GetString(reader.GetOrdinal("SetorOut")) : "";

                                    _os.idServoQExecutara = reader.IsDBNull(reader.GetOrdinal("idServoQExecutara")) == false ? reader.GetInt32(reader.GetOrdinal("idServoQExecutara")) : 0;
                                    _os.ServoQExecutara = reader.IsDBNull(reader.GetOrdinal("ServoQExecutara")) == false ? reader.GetString(reader.GetOrdinal("ServoQExecutara")) : "";

                                    _os.idAdmOut = reader.IsDBNull(reader.GetOrdinal("idAdmOut")) == false ? reader.GetInt32(reader.GetOrdinal("idAdmOut")) : 0;
                                    _os.idSetorOut = reader.IsDBNull(reader.GetOrdinal("idSetorOut")) == false ? reader.GetInt32(reader.GetOrdinal("idSetorOut")) : 0;


                                    _result.Add(_os);
                                }

                                return _result;
                            }
                        }


                    }
                }
                catch (Exception)
                {
                    return null;
                }


            }
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public void OsChangeStatus(int idOs, string aStatus)
        {
            try
            {
                using (CcbOrdemServico objDB = new CcbOrdemServico())
                {
                    objDB.ccOsAlterStatusOs(idOs, aStatus);
                }
            }
            catch (System.Exception)
            {

                throw;
            }
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Delete, true)]
        public void OsDelete(int id)
        {
            try
            {
                using (CcbOrdemServico objDB = new CcbOrdemServico())
                {
                    objDB.ccOsDelete(id);
                }
            }
            catch (System.Exception)
            {

                throw;
            }
        }
    }
}

